﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Service;
using OMG.Fill.Vegetables.Model;
using OMG.Fill.Vegetables.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Vegetables.Service
{
    /// <summary>
    /// 蔬菜冷库调查表T_VegColdStorage通用操作类
    /// </summary>
    public class VegColdStorageServices:VegColdStorages
    {
        ReportServices reportServices = new ReportServices();
        DictServices dict = new DictServices();
        public Pager<List<VegColdStorageEntity>> GetPageList(VegColdStorageQuery query)
        {
            Pager<List<VegColdStorageEntity>> obj = new Pager<List<VegColdStorageEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_VegColdStorage where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<VegColdStorageEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取蔬菜冷库调查表T_VegColdStorage分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 获取蔬菜育苗专业化组织及生产能力调查列表
        /// </summary>
        public ResResult<List<VegColdStorageEntity>> GetList(string ReportId,bool IsTranslate)
        {
            ResResult<List<VegColdStorageEntity>> obj = new ResResult<List<VegColdStorageEntity>>();
            VegColdStorageEntity model = new VegColdStorageEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                model.UnitName = "";
                model.Remark = "";
                if (AddInfo(model).status > 0)
                {
                    obj.model = new List<VegColdStorageEntity>();
                    obj.model.Add(model);
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_VegColdStorage where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<VegColdStorageEntity>(strSql, new { ReportId = ReportId }).ToList();
                        if (IsTranslate)
                        {
                            if (obj.model.Count > 0)
                            {
                                foreach (var item in obj.model)
                                {
                                    if (!string.IsNullOrEmpty(item.OrganizeType) && Common.IsNumeric(item.OrganizeType))
                                    {
                                        item.OrganizeType = string.IsNullOrEmpty(item.OrganizeType) ? "" : DataDict.Instance["UnitType"][item.OrganizeType];
                                    }
                                    else
                                    {
                                        item.OrganizeType = "无";
                                    }
                                }
                            }
                        }
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取蔬菜冷库调查表T_VegColdStorage对象信息失败", ex);

                }
            }
            return obj;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_VegColdStorage where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("判断是否生成过蔬菜冷库调查表T_VegColdStorage失败", ex);
                return false;
            }
        }
        public ResResult<int> AddInfo(List<VegColdStorageEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_VegColdStorage";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "UnitName,";
            strSql += "OrganizeType,";
            strSql += "ColdStorageNum,";
            strSql += "ColdStorageCapacity,";
            strSql += "Remark,";
            strSql += "AddUser,";
            strSql += "AddTime,";
            strSql += "UpdateUser,";
            strSql += "UpdateTime)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@UnitName,";
            strSql += "@OrganizeType,";
            strSql += "@ColdStorageNum,";
            strSql += "@ColdStorageCapacity,";
            strSql += "@Remark,";
            strSql += "@AddUser,";
            strSql += "@AddTime,";
            strSql += "@UpdateUser,";
            strSql += "@UpdateTime)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        list[i].Id = CustomFun.GetGuid();
                    }

                    string strDel = "delete from dbo.T_VegColdStorage where ReportId=@ReportId";
                    dbCon.Execute(strDel, new { ReportId = list[0].ReportId }, trans);
                    int result = dbCon.Execute(strSql, list, trans);
                    string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                    dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;

                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增蔬菜冷库调查表T_VegColdStorage对象信息失败", ex);
                    return obj;
                }
            }
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(VegNowModel model)
        {
            model.FillInYear = dict.GetKey("ReportYear", model.FillInYear);
            string ReportId = reportServices.GetReportId(model.ReportCode, model.AreaCode, model.FillInYear);
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("ColdStorage") as HSSFSheet;//读取模板所在sheet位置
            ResResult<List<VegColdStorageEntity>> pagelist = GetList(ReportId, false);//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式

            for (var i = 0; i < pagelist.model.Count; i++)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 2);
                datarow.CreateCell(0).SetCellValue(pagelist.model[i].UnitName == null ? "" : pagelist.model[i].UnitName);
                datarow.CreateCell(1).SetCellValue(pagelist.model[i].OrganizeType == null ||!Common.IsNumeric(pagelist.model[i].OrganizeType) ? "" : DataDict.Instance["UnitType"][pagelist.model[i].OrganizeType.ToString()]);
                datarow.CreateCell(2).SetCellValue(pagelist.model[i].ColdStorageNum == null ? "" : pagelist.model[i].ColdStorageNum.ToString());
                datarow.CreateCell(3).SetCellValue(pagelist.model[i].ColdStorageCapacity == null ? "" : pagelist.model[i].ColdStorageCapacity.ToString());
                datarow.CreateCell(4).SetCellValue(pagelist.model[i].Remark == null ? "" : pagelist.model[i].Remark.ToString());
                datarow.GetCell(0).CellStyle = style;
                datarow.GetCell(1).CellStyle = style;
                datarow.GetCell(2).CellStyle = style;
                datarow.GetCell(3).CellStyle = style;
                datarow.GetCell(4).CellStyle = style;
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<VegColdStorageModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<VegColdStorageModel> list = new List<VegColdStorageModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    VegColdStorageModel model = new VegColdStorageModel();
                    //model.year = row.GetCell(0).ToString();
                    if (row.GetCell(0) != null && row.GetCell(0).ToString() != "")
                    {
                        model.UnitName = row.GetCell(0).ToString();
                    }
                    else
                    {
                        model.UnitName = null;
                    }
                    if (row.GetCell(1) != null && row.GetCell(1).ToString() != "")
                    {
                        model.OrganizeType = row.GetCell(1).ToString();
                    }
                    else
                    {
                        model.OrganizeType = null;
                    }
                    if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                    {
                        model.ColdStorageNum = Convert.ToInt32(row.GetCell(2).ToString());
                    }
                    else
                    {
                        model.ColdStorageNum = null;
                    }
                    if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                    {
                        model.ColdStorageCapacity = decimal.Parse(row.GetCell(3).ToString());
                    }
                    else
                    {
                        model.ColdStorageCapacity = null;
                    }
                    if (row.GetCell(4) != null && row.GetCell(4).ToString() != "")
                    {
                        model.Remark = row.GetCell(4).ToString();
                    }
                    else
                    {
                        model.Remark = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(VegColdStorageModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_VegColdStorage";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "UnitName,";
            strSql += "OrganizeType,";
            strSql += "ColdStorageNum,";
            strSql += "ColdStorageCapacity,";
            strSql += "Remark,";
            strSql += "AddUser,";
            strSql += "AddTime,";
            strSql += "UpdateUser,";
            strSql += "UpdateTime)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@UnitName,";
            strSql += "@OrganizeType,";
            strSql += "@ColdStorageNum,";
            strSql += "@ColdStorageCapacity,";
            strSql += "@Remark,";
            strSql += "@AddUser,";
            strSql += "@AddTime,";
            strSql += "@UpdateUser,";
            strSql += "@UpdateTime)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增蔬菜冷库调查表T_VegColdStorage对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        public ResResult<int> DeleteData(string id)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"delete from dbo.T_VegColdStorage where ReportId = @Id";
            int result;
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Execute(strSql, new { Id = id });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "删除成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "删除失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("删除蔬菜冷库调查表T_VegColdStorage对象信息失败", ex);
                return obj;
            }
        }
        public ResResult<List<VegColdStorageStatsModel>> GetStatsList(VegNowModel model)
        {
            DictServices dictServices = new DictServices();
            ResResult<List<VegColdStorageStatsModel>> result = new ResResult<List<VegColdStorageStatsModel>>();
            var paramlist = new DynamicParameters();
            if (!string.IsNullOrEmpty(model.AreaCode))
            {
                paramlist.Add("AreaCode", model.AreaCode);
            }
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetKey("ReportYear", model.FillInYear);
                paramlist.Add("FillInYear", model.FillInYear);
            }
            if (!string.IsNullOrEmpty(model.Status))
            {
                paramlist.Add("Status", model.Status);
            }

            string strSql = "select  a.Code AreaCode,FullName AreaName,Coord,ColdStorageNum,ColdStorageCapacity  from   ";
            strSql += "(select ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strSql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode    ";
            }
            else
            {
                strSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code    ";
            }
            strSql += ",sum(ISNULL(ColdStorageNum,0)) ColdStorageNum       ";//--育苗组织性质
            strSql += ",sum(ISNULL(ColdStorageCapacity,0)) ColdStorageCapacity       ";//--育苗面积
            //strSql += ",GrowSeedleNum       ";//--年育苗销售株树（含自用）（万株）
            //strSql += ",remark       ";//--//备注
            strSql += "from   [dbo].[T_Report] Report       ";//
            strSql += "left join[dbo].[T_VegColdStorage] VegColdStorage on Report.Id = VegColdStorage.ReportId       ";//
            strSql += "right join T_Area Area on Area.Code = Report.AreaCode       ";//
            strSql += "where       ";//
            strSql += "Report.FillInYear = @FillInYear   ";//
            if (!string.IsNullOrEmpty(model.OrganizeType))
            {
                paramlist.Add("OrganizeType", model.OrganizeType);
                strSql += "and OrganizeType = @OrganizeType    ";
            }
            strSql += "and Report.Status = @Status    ";//--表示审核通过";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strSql += " and Area.ParentCode!= '" + DefaultKey.ProvinceCode + "' group by rollup(Area.ParentCode)    ";
                strSql += ")t1 left join T_Area a on t1.ParentCode=a.Code   ";
            }
            else
            {
                if (model.AreaCode.Substring(4, 1) == "0")
                {
                    strSql += "and   Area.ParentCode=  @AreaCode   ";//州市
                }
                else
                {
                    strSql += "and   Area.Code=  @AreaCode ";//区县
                }
                strSql += "group by rollup(Area.Code)    ";
                strSql += ")t1 left join T_Area a on t1.Code=a.Code   ";
            }
            strSql += " order by a.Code";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //查询去年数据
                    List<VegColdStorageStatsModel> list = new List<VegColdStorageStatsModel>();
                    //VegColdStorageStatsModel totalModel = new VegColdStorageStatsModel();
                    //totalModel.ColdStorageCapacity = decimal.Zero;
                    //totalModel.ColdStorageNum = 0;
                    list = dbCon.Query<VegColdStorageStatsModel>(strSql, paramlist).ToList();
                    if (list.Count > 0)
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            if (!string.IsNullOrEmpty(model.OrganizeType))
                            {
                                list[i].OrganizeType = DataDict.Instance["UnitType"][model.OrganizeType];
                            }
                            else
                            {
                                list[i].OrganizeType = "全部";
                            }
                            //    totalModel.ColdStorageCapacity += list[i].ColdStorageCapacity == null ? 0 : list[i].ColdStorageCapacity;
                            //    totalModel.ColdStorageNum += list[i].ColdStorageNum == null ? 0 : list[i].ColdStorageNum;
                            //}
                            //totalModel.UnitName = "合计";
                            //totalModel.OrganizeType = "全部";
                            //list.Add(totalModel);
                        }
                        result.model = list;
                        result.status = DefaultKey.SuccessResult;//初始化成功status为1
                        result.message = "VegTecPromotion报表统计数据获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "VegTecPromotion报表统计数据获取失败" + ex.ToString().Trim();
            }
            return result;
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream MethodStats(VegNowModel model)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegStatsDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("ColdStorage") as HSSFSheet;//读取模板所在sheet位置
            List<VegColdStorageStatsModel> list = GetStatsList(model).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式

            for (var i = 0; i < list.Count; i++)
            {
                HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 2);
                datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName);
                datarow.CreateCell(1).SetCellValue(list[i].UnitName == null ? "" : list[i].UnitName);
                datarow.CreateCell(2).SetCellValue(list[i].OrganizeType);
                datarow.CreateCell(3).SetCellValue(list[i].ColdStorageNum == null ? "" : list[i].ColdStorageNum.ToString());
                datarow.CreateCell(4).SetCellValue(list[i].ColdStorageCapacity == null ? "" : list[i].ColdStorageCapacity.ToString());
                datarow.CreateCell(5).SetCellValue(list[i].Remark == null ? "" : list[i].Remark.ToString());
                datarow.GetCell(0).CellStyle = style;
                datarow.GetCell(1).CellStyle = style;
                datarow.GetCell(2).CellStyle = style;
                datarow.GetCell(3).CellStyle = style;
                datarow.GetCell(4).CellStyle = style;
                datarow.GetCell(5).CellStyle = style;
            }
            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
    }
}